﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;

namespace WebApplication10.App_Code
{
    public class Employer
    {
        public string Username { get; set; }
        public string Password { get; set; }
        public int EmployerID { get; set; }
        public string Name { get; set; }
        public int EmployerContact { get; set; }
        public string EmployerEmail { get; set; }
        public string JobPosition { get; set; }
        public int FeeID { get; set; }

        public int CompanyID { get; set; }
        public string CompanyName { get; set; }
        public string CompanyDescription { get; set; }
        public string Address { get; set; }
        public int PostalCode { get; set; }
        public string CompanyContact { get; set; }
        public string CompanyEmail { get; set; }

        public static Employer GetEmployerByID(string EmployerID)
        {
            SqlConnection conn = new SqlConnection();
            Employer em = null;

            try
            {
                conn = new SqlConnection();
                String connstr = ConfigurationManager.ConnectionStrings["BlueSearchDBString"].ToString();
                conn.ConnectionString = connstr;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from Employer where EmployerID=@EmployerID";
                comm.Parameters.AddWithValue("@EmployerID", EmployerID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    em = new Employer();
                    em.Username = (string)dr["Username"];
                    em.Password = (string)dr["Password"];
                    em.EmployerID = (int)dr["EmployerID"];
                    em.Name = (string)dr["Name"];
                    em.EmployerContact = (int)dr["EmployerContact"];
                    em.EmployerEmail = (string)dr["EmployerEmail"];
                    em.JobPosition = (string)dr["JobPostion"];
                    em.FeeID = (int)dr["FeeID"];
                    em.CompanyName = (string)dr["Companyname"];
                    em.CompanyEmail = (string)dr["CompanyEmail"];
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return em;
        }

        public static Employer GetEmployerByUsername(string Username)
        {
            SqlConnection conn = new SqlConnection();
            Employer em = null;

            try
            {
                conn = new SqlConnection();
                String connstr = ConfigurationManager.ConnectionStrings["BlueSearchDBString"].ToString();
                conn.ConnectionString = connstr;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from Employer where Username=@Username";
                comm.Parameters.AddWithValue("@Username", Username);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    em = new Employer();
                    em.Username = (string)dr["Username"];
                    em.Password = (string)dr["Password"];
                    em.EmployerID = (int)dr["EmployerID"];
                    //em.Name = (string)dr["Name"];
                    //em.EmployerContact = (int)dr["EmployerContact"];
                    //em.EmployerEmail = (string)dr["EmployerEmail"];
                    //em.JobPosition = (string)dr["JobPostion"];
                    //em.FeeID = (int)dr["FeeID"];
                    //em.CompanyName = (string)dr["Companyname"];
                    //em.CompanyEmail = (string)dr["CompanyEmail"];
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return em;
        }

        public static void UpdateEmployer(Employer em)
        {
            SqlConnection conn = new SqlConnection();

            try
            {
                conn = new SqlConnection();
                string connstr = ConfigurationManager.ConnectionStrings["BlueSearchDBString"].ToString();
                conn.ConnectionString = connstr;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText =
                    "Update Employer SET Username=@Username, Password=@Password, Name=@Name, EmployerContact=@EmployerContact, EmployerEmail=@EmployerEmail, JobPostition=@JobPosition, FeeID=@FeeID, CompanyName=@CompanyName, CompanyEmail=@CompanyEmailWHERE EmployerID=@EmployerID";
                comm.Parameters.AddWithValue("@Username", em.Username);
                comm.Parameters.AddWithValue("@Password", em.Password);
                comm.Parameters.AddWithValue("@Name", em.Name);
                comm.Parameters.AddWithValue("@EmployerContact", em.EmployerContact);
                comm.Parameters.AddWithValue("@EmployerEmail", em.EmployerEmail);
                comm.Parameters.AddWithValue("@JobPosition", em.JobPosition);
                comm.Parameters.AddWithValue("@FeeID", em.FeeID);
                comm.Parameters.AddWithValue("@CompanyName", em.CompanyName);
                comm.Parameters.AddWithValue("@CompanyEmail", em.CompanyEmail);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        public static void DeleteEmployer(Employer em)
        {
            SqlConnection conn = new SqlConnection();

            try
            {
                conn = new SqlConnection();
                string connstr = ConfigurationManager.ConnectionStrings["BlueSearchDBString"].ToString();
                conn.ConnectionString = connstr;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText =
                    "Delete from Employer WHERE EmployerID=@EmployerID";
                comm.Parameters.AddWithValue("@EmployerID", em.EmployerID);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public static int InsertEmployer(Employer em)
        {
            SqlConnection conn = new SqlConnection();
            int newEmployerID = 0;

            try
            {
                conn = new SqlConnection();
                string connstr = ConfigurationManager.ConnectionStrings["BlueSearchDBString"].ToString();
                conn.ConnectionString = connstr;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Insert into Employer (Username, Password, Name, EmployerContact, EmployerEmail, JobPosition, FeeID, CompanyName, CompanyEmail) VALUES (@Username, @Password, @Name, @EmployerContact, @EmployerEmail, @JobPosition, @FeeID, @CompanyName, @CompanyEmail)";
                comm.Parameters.AddWithValue("@Username", em.Username);
                comm.Parameters.AddWithValue("@Password", em.Password);
                comm.Parameters.AddWithValue("@Name", em.Name);
                comm.Parameters.AddWithValue("@EmployerContact", em.EmployerContact);
                comm.Parameters.AddWithValue("@EmployerEmail", em.EmployerEmail);
                comm.Parameters.AddWithValue("@JobPosition", em.JobPosition);
                comm.Parameters.AddWithValue("@FeeID", em.FeeID);
                comm.Parameters.AddWithValue("@CompanyName", em.CompanyName);
                comm.Parameters.AddWithValue("@CompanyEmail", em.CompanyEmail);

                int rowsAffected = comm.ExecuteNonQuery();
                comm.CommandText = "select @@IDENTITY as newEmployerID";
                SqlDataReader dr = comm.ExecuteReader();
                dr.Read();
                newEmployerID = int.Parse(dr["NewEmployerID"].ToString());
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return newEmployerID;
        }

        public static Employer GetCompanyByID(string CompanyID)
        {
            SqlConnection conn = new SqlConnection();
            Employer emuser = null;
            try
            {
                conn = new SqlConnection();
                string connstr = ConfigurationManager.ConnectionStrings["BlueSearchDbString"].ToString();
                conn.ConnectionString = connstr;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from Company where CompanyID=@CompanyID";
                comm.Parameters.AddWithValue("@CompanyID", CompanyID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    emuser = new Employer();
                    emuser.CompanyID = (int)dr["CompanyID"];
                    emuser.CompanyName = (string)dr["CompanyName"];
                    emuser.CompanyDescription = (string)dr["CompanyDescription"];
                    emuser.Address = (string)dr["Address"];
                    emuser.PostalCode = (int)dr["PostalCode"];
                    emuser.CompanyContact = (string)dr["CompanyContact"];
                    emuser.CompanyEmail = (string)dr["CompanyEmail"];
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return emuser;
        }

        public static void UpdateCompany(Employer em)
        {
            SqlConnection conn = new SqlConnection();

            try
            {
                conn = new SqlConnection();
                string connstr = ConfigurationManager.ConnectionStrings["BlueSearchDbString"].ToString();
                conn.ConnectionString = connstr;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText =
                    "Update Employer SET CompanyName=@CompanyName, CompanyDescription=@CompanyDescription, Address=@Address, PostalCode=@PostalCode, CompanyContact=@CompanyContact, CompanyEmail=@CompanyEmail WHERE CompanyID=@CompanyID";
                comm.Parameters.AddWithValue("@CompanyName", em.CompanyName);
                comm.Parameters.AddWithValue("@CompanyDescription", em.CompanyDescription);
                comm.Parameters.AddWithValue("@Address", em.Address);
                comm.Parameters.AddWithValue("@PostalCode", em.PostalCode);
                comm.Parameters.AddWithValue("@CompanyContact", em.CompanyContact);
                comm.Parameters.AddWithValue("@CompanyEmail", em.CompanyEmail);
                comm.Parameters.AddWithValue("@CompanyID", em.CompanyID);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public static void DeleteCompany(Employer c)
        {
            SqlConnection conn = new SqlConnection();

            try
            {
                conn = new SqlConnection();
                string connstr = ConfigurationManager.ConnectionStrings["BlueSearchDbString"].ToString();
                conn.ConnectionString = connstr;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText =
                    "Delete from Company WHERE CompanyID=@CompanyID";
                comm.Parameters.AddWithValue("@CompanyID", c.CompanyID);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public static int InsertCompany(Employer c)
        {
            SqlConnection conn = null;
            int newCompanyID = 0;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["BlueSearchDbString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Insert into Company (CompanyName, CompanyDescription, Address, PostalCode, CompanyContact, CompanyEmail) VALUES (@CompanyName, @CompanyDescription, @Address, @PostalCode, @CompanyContact, @CompanyEmail)";
                comm.Parameters.AddWithValue("@CompanyName", c.CompanyName);
                comm.Parameters.AddWithValue("@CompanyDescription", c.CompanyDescription);
                comm.Parameters.AddWithValue("@Address", c.Address);
                comm.Parameters.AddWithValue("@PostalCode", c.PostalCode);
                comm.Parameters.AddWithValue("@CompanyContact", c.CompanyContact);
                comm.Parameters.AddWithValue("@CompanyEmail", c.CompanyEmail);
                int rowsAffected = comm.ExecuteNonQuery();
                comm.CommandText = "select @@IDENTITY as newCompanyID";
                SqlDataReader dr = comm.ExecuteReader();
                dr.Read();
                newCompanyID = int.Parse(dr["NewCompanyID"].ToString());
                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return newCompanyID;
        }
    }
}
